package org.sraosha.creator.manager;

import org.sraosha.creator.constants.DatabaseConstants;
import com.alibaba.druid.pool.DruidDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 数据库管理类
 */
public class MysqlManager implements IDatabaseManager {

    private Connection connection;
    private PreparedStatement preparedStatement;

    public MysqlManager() {
        try {
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setUrl(DatabaseConstants.MYSQL_URL);
            dataSource.setDriverClassName(DatabaseConstants.MYSQL_DRIVER_CLASS_NAME);
            dataSource.setUsername(DatabaseConstants.MYSQL_USERNAME);
            dataSource.setPassword(DatabaseConstants.MYSQL_PASSWORD);
            connection = dataSource.getConnection();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 关闭preparedStatement和connection
     */
    @Override
    public void close() {
        try {
            this.preparedStatement.close();
            this.connection.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 返回当前用户的所有表的信息
     *
     * @return
     */
    @Override
    public ResultSet findUserTable() {
        try {
            preparedStatement = connection.prepareStatement("SHOW TABLES");
            return preparedStatement.executeQuery();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    /**
     * 根据表名，返回表的所有列的信息
     *
     * @param tableName
     * @return
     */
    @Override
    public ResultSet findTableColumnByTableName(String tableName) {
        try {
            preparedStatement = connection.prepareStatement("SELECT * FROM information_schema.COLUMNS WHERE table_schema='" + DatabaseConstants.MYSQL_DATABASE_NAME + "' and table_name=?");
            preparedStatement.setString(1, tableName.toUpperCase());
            ResultSet resultSet = preparedStatement.executeQuery();
            return resultSet;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    /**
     * 根据表名称返回表的注释
     *
     * @param tableName
     * @return
     */
    @Override
    public String findCommentByTableName(String tableName) {
        try {
            preparedStatement = connection.prepareStatement("SHOW TABLE STATUS FROM " + DatabaseConstants.MYSQL_DATABASE_NAME + " WHERE Name=?");
            preparedStatement.setString(1, tableName.toUpperCase());
            ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.next();
            return resultSet.getString(18);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    /**
     * 根据表名和列名，返回注解
     *
     * @param tableName
     * @param columnName
     * @return
     */
    @Override
    public String findCommentByTableNameAndColumnName(String tableName, String columnName) {
        try {
            preparedStatement = connection.prepareStatement("SHOW FULL COLUMNS FROM `" + tableName.toUpperCase() + "` where Field=?");
            preparedStatement.setString(1, columnName.toUpperCase());
            ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.next();
            return resultSet.getString(9);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    /**
     * 返回表名
     * @param resultSet
     * @return
     */
    @Override
    public String getTableName(ResultSet resultSet) {
        try {
            return (String) resultSet.getObject(1);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 返回列名
     * @param resultSet
     * @return
     */
    @Override
    public String getColumnName(ResultSet resultSet) {
        try {
            return resultSet.getString(4);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 返回列类型
     * @param resultSet
     * @return
     */
    @Override
    public String getColumnType(ResultSet resultSet) {
        try {
            return resultSet.getString(8);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 返回主键
     * @param resultSet
     * @return
     */
    @Override
    public String getIdentityColumn(ResultSet resultSet) {
        try {
            return resultSet.getString(17);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
